package com.util;



import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


/**
 * @author DingYuanWei 
 * @version 創建時間：Apr 13, 2009 1:49:49 PM
 * 類說明:Excel工具類(多加入一个功能--统计函数（Sum）)
 * 功能说明：导入Excel,导出Excel,自定义Excel、统计函数（Sum）
 */
public class ExcelUtil{
	
	private static final Log log = LogFactory.getLog(ExcelUtil.class);//日志

	/**
	 * 創建Excel
	 * @param excelBean
	 * @return
	 */
	public static HSSFWorkbook creatExcel(HSSFWorkbook hssfwork, ExcelBean excelBean){
		HSSFWorkbook wb = hssfwork; //創建Excel工作薄
		//在Excel工作薄中建一工作表，默認為缺省值
//		HSSFSheet sheet = wb.createSheet();
        HSSFSheet sheet = wb.createSheet(excelBean.getSheetName());
        //設置工作表各列寬度
       // setColumnWidth(sheet, excelBean.getWidth());
        //設置字體，樣式
        //HSSFFont font = setFont(wb, "宋體");
        //HSSFCellStyle style = setCellStyle(wb, font);
        //設置第一行
        List<String> titleList = excelBean.getTitleList();
        setRowTitle(sheet, titleList, 0); //在索引0的位置創建行（最頂端的行）
        //設置數據行
        List dataList = excelBean.getDataList();
        int a = 1;
        if(dataList != null){
        	for(int i=0; i<dataList.size(); i++){        		
        		List rowList = (List) dataList.get(i);
        		setRowValue(sheet, rowList, i+1); //在索引1的位置創建行（第2行）   
        		a = a + 1;
        	}
        }
        
        List<Object> formula = excelBean.getFormula();
        
        if(formula != null){
        	setRowFormula(sheet,formula,a);
        }
        return wb;
	}
	
	/**
	 * 設置工作表各列寬度
	 * @param sheet
	 * @param width
	 */
	public static void setColumnWidth(HSSFSheet sheet, int[] width){
        for(int i=0;i<width.length;i++){
            sheet.setColumnWidth((short)i, (short)(width[i]*256));
        }
    }
	
	/**
	 * 設置字體
	 * @param wb
	 * @param fontName 
	 */
	public static HSSFFont setFont(HSSFWorkbook wb, String fontName){
		HSSFFont font = wb.createFont();
		font.setFontName(fontName); //宋體， 
		return font;		
	}
	
	/**
	 * 將字體對象賦值給單格樣式對象
	 * @param wb
	 * @param font
	 * @return
	 */
	public static HSSFCellStyle setCellStyle(HSSFWorkbook wb, HSSFFont font){
		HSSFCellStyle style = wb.createCellStyle();
        style.setFont(font);
        return style;
	}
	
	/**
	 * Excel行標題 類型String
	 * @param sheet
	 * @param list
	 * @param rowNo
	 * @return
	 */
	public static HSSFSheet setRowTitle(HSSFSheet sheet, List<String> list,int rowNo){
		if(list != null){
			//在索引rowNo的位置創建行
			HSSFRow rowTitle = sheet.createRow(rowNo);
			for(int i= 0 ; i<list.size();i++){
				String title = list.get(i);
				//從索引0的位置開始創建單元格（左上端）
				HSSFCell cell = rowTitle.createCell((short)i);
				cell.setCellValue(title); //設置單元格內容
			}
		}
		return sheet;
	}
	
	
	/**
	 * 因為混合數據，String Integer 所以要進行判斷，然後統計
	 * @param sheet
	 * @param list
	 * @param rowNo
	 * @return
	 */
	public static HSSFSheet setRowValue(HSSFSheet sheet, List list,int rowNo){
		if(list != null){
			//在索引rowNo的位置創建行
			HSSFRow rowTitle = sheet.createRow(rowNo);
			for(int i= 0 ; i<list.size();i++){
				Object value = list.get(i);
				if(value instanceof String){
					String s = (String)value;
					HSSFCell cell = rowTitle.createCell((short)i);
					cell.setCellValue(s); //設置單元格內容
					
				}
				else if(value instanceof Integer){
					//從索引0的位置開始創建單元格（左上端）
					Integer in = (Integer)value;
					HSSFCell cell = rowTitle.createCell((short)i);
					cell.setCellValue(in); //設置單元格內容
					
				}
			}
		}
		return sheet;
	}
	
	/**
	 * 設置行值(加样式的)
	 * @param sheet
	 * @param List
	 * @return
	 */
	public static HSSFSheet setRowValue(HSSFSheet sheet, List<String> list,
			int rowNo, short encoding, HSSFCellStyle style){
		if(list != null){
			//在索引0的位置創建行（最頂端的行）
			HSSFRow rowTitle = sheet.createRow(rowNo);
			for(int i= 0 ; i<list.size();i++){
				String title = list.get(i);
				//從索引0的位置開始創建單元格（左上端）
				HSSFCell cell = rowTitle.createCell((short)i);
				cell.setCellValue(title); //設置單元格內容
				cell.setEncoding(encoding);//設置編碼 HSSFCell.ENCODING_UTF_16
				cell.setCellStyle(style);//單元格樣式  
			}
		}
		return sheet;
	}
	
	/**
	 * Excel統計sum 因此只對sum函數 進行判斷
	 * @param sheet
	 * @param list
	 * @param rowNo
	 * @return
	 */
	public static HSSFSheet setRowFormula(HSSFSheet sheet, List<Object> list,int rowNo){
		if(list != null){
			//在索引rowNo的位置創建行
			HSSFRow rowTitle = sheet.createRow(rowNo);
			for(int i= 0 ; i<list.size();i++){
				Object title = (Object)list.get(i);
				//從索引0的位置開始創建單元格（左上端）
				HSSFCell cell = rowTitle.createCell((short)i);
				if(title instanceof Integer){
					Integer t = (Integer)title;
					cell.setCellValue(t);
				}
				else{
					String s = (String)title;
					s = s.toUpperCase();
					if(s.indexOf("SUM")> -1){
						cell.setCellFormula(s);//設置單元格內容(处理函数设定SUM(A1:B2)
					}
					else{
						cell.setCellValue(s);
					}
				}
			}
		}
		return sheet;
	}

	/**
	 * 寫入到物理地址中
	 * @param wb
	 * @param path
	 * @return
	 */
	public static boolean writeExcel(HSSFWorkbook wb, String path){
		boolean flag = true;
		try{
            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream(path);
            wb.write(fileOut);
            fileOut.close();
            log.info("Excel寫入到" + path + ", 成功！");
        }catch(Exception e){
        	log.error("Excel寫入到" + path + ", 失敗！");
        	flag = false;
            e.printStackTrace();
        }
        return flag;
	}
	
	/**
	 * 下載Excel(Web应用)
	 * @param wb
	 * @param filename MemberList.xls
	 * @param response
	 * @return
	 */
	public static boolean downloadExcel(HSSFWorkbook wb, String filename, HttpServletResponse response){
		boolean flag = true;		
		//設置輸入流
		OutputStream output = null;
		try {
//			設置響應類型
			response.setHeader("Content-disposition", "attachment;filename="+new String(filename.getBytes("GBK"),"ISO-8859-1"));
			response.setContentType("application/vnd.ms-excel");
			
			output = response.getOutputStream();			
			wb.write(output);//輸出到網頁中
			output.flush();
			log.info("下載Excel：" + filename + ", 成功！");
		} catch (Exception e) {
			flag = false;
			log.info("下載Excel：" + filename + ", 失敗！");
			e.printStackTrace();
		} finally{
			try {
				output.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return flag;		
	}
//+++++++++++++++++从这开始是对复杂excel进行处理的方法    ----(start)++++++++++
	/**
	 * 設置單元格式
	 * @author huangyonghua 2010-11-2
	 * @param wb
	 * @return
	 */
	public static HSSFCellStyle createHcs(HSSFWorkbook wb){
		HSSFCellStyle normalStyle = wb.createCellStyle();		
		normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		normalStyle.setWrapText(true);
		return normalStyle;
	}
	
	/**
	 * 取得map中的個數
	 * 因有兩種類型 Integer LinkedHashMap 所以需要判斷
	 * @param map
	 * @return
	 */
	private static int getMapSize(Map map) {
		int mapSize = 0;
		for (Iterator iter = map.entrySet().iterator(); iter.hasNext(); ) {
			Entry ent = (Entry) iter.next();
			Object o = ent.getValue();
			if(o instanceof Integer){
				mapSize += (Integer)o;
			}
			else if(o instanceof LinkedHashMap){
				Map m = (LinkedHashMap)o;
				if (m != null && !m.isEmpty()) {
					int size = m.size();//m !=null
					mapSize += size;
				}				
			}
		}
		return mapSize;
	}
	
	
	/**
	 * excelBean 主要設置 
	 * excelBean.setSheetName("Click");
	 * excelBean.setTitleMaps(titleMaps);
	 * excelBean.setDataList(dataList);
	 * @param excelBean
	 * @param num 1(代表只有标题进行合并)/2（代表有标题进行合并和下一标题进行不同的程度处理）
	 * @param title
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static HSSFWorkbook creatExcelHasTitleAndRegion(ExcelBean excelBean,int num,String title) {		
		HSSFWorkbook wb = new HSSFWorkbook(); // 創建excel文檔
		HSSFCellStyle normalStyle = createHcs(wb);		
		HSSFSheet st = wb.createSheet(excelBean.getSheetName()); // 創建工作薄
		HSSFRow row = null;
		HSSFCell cell = null;
		Map map = excelBean.getTitleMaps();
		int i = 0;
		if(title != null){
			i = 1;
			st.addMergedRegion(new Region(0,(short)0,0,(short)(getMapSize(map)-1)));
			row = st.createRow(0);
			cell = row.createCell((short) 0);
			cell.setCellValue(new HSSFRichTextString(title));
			cell.setCellStyle(normalStyle);
		}
		//簡單單元 無合併
		if(num == 1){
			//setColumnWidth(st, excelBean.getWidth());//設置column寬度
			if(map != null && !map.isEmpty()){
				int j = 0;
				for (Iterator iter = map.entrySet().iterator(); iter.hasNext(); ) {
					Entry ent = (Entry) iter.next();
					String name = (String)ent.getKey();
					st.addMergedRegion(new Region(i, (short) j, i, (short) j));
					row = st.createRow(i);
					cell = row.createCell((short) j);
					cell.setCellValue(new HSSFRichTextString(name));//HSSFRichTextString可以处理中文字符
					cell.setCellStyle(normalStyle);
					j++;
				}
			}
		}
		//複雜單元 合併 
		else if (num == 2) {
			if(map != null && !map.isEmpty()){
				int j = 0;
				for (Iterator iter = map.entrySet().iterator(); iter.hasNext(); ) {
					Entry ent = (Entry) iter.next();
					Object o = ent.getValue();
					String name = (String)ent.getKey();
					if(o instanceof Integer){
						st.addMergedRegion(new Region(i, (short) j, i+1, (short) j));
						row = st.createRow(i);
						cell = row.createCell((short) j);
						cell.setCellValue(new HSSFRichTextString(name));
						cell.setCellStyle(normalStyle);
						j++;
					}
					else if(o instanceof LinkedHashMap){
						Map m = (LinkedHashMap)o;
						int size = m.size();//m !=null
						st.addMergedRegion(new Region(i, (short) j, i, (short) (j+size-1)));
						HSSFCell cell2 = st.createRow(i).createCell((short) j);
						cell2.setCellValue(new HSSFRichTextString(name));
						cell2.setCellStyle(normalStyle);
						int k = 0;
						for (Iterator it = m.entrySet().iterator(); it.hasNext(); ) {
							Entry e = (Entry) it.next();
							HSSFCell cell3 = st.createRow(i+1).createCell((short) (j+k));
							cell3.setCellValue(new HSSFRichTextString((String)e.getKey()));
							cell3.setCellStyle(normalStyle);
							k++;
						}
						j += size;
					}
				}
			}
		}		
		
		// 設置數據
		List dataList = excelBean.getDataList();
        if(dataList != null){
        	for(int m=0; m<dataList.size(); m++){        		
        		List rowList = (List) dataList.get(m);        		  
        		setRowValue(st, rowList, num + i + m, HSSFCell.ENCODING_UTF_16, normalStyle);//在索引num+i+m的位置創建行（第num+i+m+1行）  
        	}
        }
		return wb;
	}
	
//	+++++++++++++++++从这开始是对复杂excel进行处理的方法结束    ----(end)++++++++++	

	
	
/*  導入文件（excel）   **/
	/**
	 * 導入文件（excel）
	 * @author dingyuanwei
	 * @param path   路徑+檔名
	 * @param line   從第幾欄開始（写出的Excel）
	 * @param rowLen 到第几列结束
	 * @param atRow 从第几行开始写（写入的Excel）
	 * @return list 返回一个包含行的LIST对象记录
	 * @return inputStream 将excel进行流化
	 * @throws Exception
	 * 解析Excel文件
	 */
	public static List<Object> importData(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{  
		List<Object> tmpList = new ArrayList<Object>();  
		POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
		HSSFWorkbook wb = new HSSFWorkbook(pfs);
		HSSFSheet sheet = wb.getSheetAt(0);//第0索引文件解析
		HSSFRow row = null;
		try{   
			int totalRow = sheet.getLastRowNum(); //excel資料行數
			if(totalRow>0){ //判斷excel是否為空
				for(int i = atRow ; i <=totalRow; i++){
			    	List<Object> rowList = new ArrayList<Object>();  
			    	row = sheet.getRow(i);
			    	if(row!=null){ //判断行是否为空
			    		for(int j = line; j < rowLen; j++){  
							HSSFCell cell = row.getCell((short) j);
							if(cell == null){ //判断列是否为空
								rowList.add(""); 
							}else{
								int ctype = cell.getCellType();
								switch(ctype){
									// 處理string部份
									case HSSFCell.CELL_TYPE_BLANK:
										rowList.add("");
									break;
									
									case HSSFCell.CELL_TYPE_STRING:
										rowList.add(cell.getRichStringCellValue().getString().trim());
									break;
									
									//處理number部份
									case HSSFCell.CELL_TYPE_NUMERIC:
										
										if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型
											System.out.print( cell.getDateCellValue());
											//須轉date格式
											rowList.add(cell.getDateCellValue()); 
									    }else{
									    	
											double tmpdouble = cell.getNumericCellValue();
											if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) { 
												
												//int 
												rowList.add(Integer.toString((int)tmpdouble)); 
											}else{ 
												
												//double 
												rowList.add(Double.toString(cell.getNumericCellValue())); 
											}
									    }
									break;
									
									//處理boolean部份
									case HSSFCell.CELL_TYPE_BOOLEAN:  
										rowList.add(cell.getBooleanCellValue()); 
									break;
								} 
							}  
				    	} 
				    	tmpList.add(rowList);//加入每一行，行中又包含单元格字段
			    	}
			    }
			}
		}catch(Exception e){ 
			e.printStackTrace();
			throw e;
		} 
	    return tmpList;//所有的行
	}
	
	//取得字符：SUM(A2:A13)
	private static String getSumAB(int xfrom,int yfrom,int length){
		//sum(a2:a11);
		String str = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		int m = ((xfrom)/26) -1;
		char a = str.charAt(xfrom%26);
		return "SUM("+(m == -1?"":str.charAt(m))+a+yfrom+":"+(m == -1?"":str.charAt(m))+a+(yfrom+length)+")";
	}
	

}

